package com.gopay.common.cps.dao.wallet.tyy.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.gopay.common.base.dao.impl.BaseDAOImpl;
import com.gopay.common.cps.dao.wallet.tyy.WaTyyCommonDao;

/**
 * 
 * @author tangyang
 * @date 20170419
 */
@Repository("waTyyCommonDao")
public class WaTyyCommonDaoImpl extends BaseDAOImpl<Object[], Integer> implements WaTyyCommonDao {
    
    @Override
    public List<Object[]> queryInviteInfos(Map<String, Object> param, int pageIndex, int pageSize) {
        Map<String, Object> obj = getWhereSql(param);
        StringBuffer sb = new StringBuffer(" select ");
      //0注册时间
        sb.append(" info.register_ts, ");
      //1邀请时间
        sb.append(" info.receive_ts, ");
      //2受邀人名称
        sb.append(" recv.cust_name, ");
      //3受邀人手机号
        sb.append(" info.receive_cust_mob, ");
      //4受邀人所在地
        sb.append(" info.receive_cust_address, ");
      //5受邀人IP
        sb.append(" info.receive_cust_ip, ");
      //6邀请人名称
        sb.append(" info.invite_cust_name, ");
      //7邀请人手机号
        sb.append(" info.invite_cust_mob, ");
      //8邀请人所在地
        sb.append(" info.invite_cust_address, ");
      //9邀请人所在地
        sb.append(" info.invite_cust_ip, ");
      //10是否注册
        sb.append(" info.is_register, ");
      //11邀请状态
        sb.append(" info.invite_status, ");
      //12实名认证状态
        sb.append(" recv.is_real_name_certify, ");
      //13备注
        sb.append(" info.remark ");
        sb.append(getSearchSql());
        sb.append(obj.get("sql"));
        sb.append(" order by info.receive_ts desc ");
        return findBySql(sb.toString(), obj.get("param")==null?new HashMap<String, Object>():(Map<String, Object>)obj.get("param"), pageIndex, pageSize);

    }
    
    @Override
    public List<Object[]> queryInviteInfos(Map<String, Object> param) {
        Map<String, Object> obj = getWhereSql(param);
        StringBuffer sb = new StringBuffer(" select ");
        sb.append(" info.register_ts, ");//0
        sb.append(" info.receive_ts, ");//1
        sb.append(" recv.cust_name, ");//2
        sb.append(" info.receive_cust_mob, ");//3
        sb.append(" info.receive_cust_address, ");//4
        sb.append(" info.receive_cust_ip, ");//5
        sb.append(" info.invite_cust_name, ");//6
        sb.append(" info.invite_cust_mob, ");//7
        sb.append(" info.invite_cust_address, ");//8
        sb.append(" info.invite_cust_ip, ");//9
        sb.append(" info.is_register, ");//10
        sb.append(" info.invite_status, ");//11
        sb.append(" recv.is_real_name_certify, ");//12
        sb.append(" info.remark ");//13
        sb.append(getSearchSql());
        sb.append(obj.get("sql"));
        sb.append(" order by info.receive_ts desc ");
        return findBySql(sb.toString(), obj.get("param")==null?new HashMap<String, Object>():(Map<String, Object>)obj.get("param"));

    }
    
    @Override
    public int queryInviteInfosCount(Map<String, Object> params) {
        Map<String, Object> obj = getWhereSql(params);
        StringBuffer sb = new StringBuffer("SELECT ");
        sb.append(" COUNT(*) ");
        sb.append(getSearchSql());
        sb.append(obj.get("sql"));
        return findCountBySql(sb.toString(),obj.get("param")==null?new HashMap<String, Object>():(Map<String, Object>)obj.get("param"));
    }
    
    private String getSearchSql(){
        StringBuffer sb = new StringBuffer();
        sb.append(" from wa_tyy_invite_info info ");
        sb.append(" left join cust_person_info recv on info.receive_cust_id = recv.cust_id ");
        sb.append(" where 1=1 ");
        return sb.toString();
    }
    /**
     * 生成HSQL语句的Where部分，同时负责提供相应的参数值用于查询。
     * @param param 客户发出请求的各种参数汇总。
     * @return 包含两部分信息的Map对象。
     *              sql部分包含了HSQL语句的where部分，param部分包含了HSQL参数对应的参数值。
     */
    private Map<String, Object> getWhereSql(Map<String, Object> param){
        Map<String, Object> res = new HashMap<String, Object>();
        
        Map<String, Object> nparam = new HashMap<String, Object>();
        StringBuffer sb = new StringBuffer();

        if(param.get("submitTimeStart")!=null ){
            String startDt = ((String)param.get("submitTimeStart")).replaceAll("-", "");
            sb.append(" AND info.register_ts >= to_date(:startDt,'yyyy/MM/dd HH24:mi:ss') ");
            nparam.put("startDt", startDt+" 000000");
        }
        
        if(param.get("submitTimeEnd")!=null ){
            String endDt = ((String)param.get("submitTimeEnd")).replaceAll("-", "");
            sb.append(" AND info.register_ts <= to_date(:endDt,'yyyy/MM/dd HH24:mi:ss') ");
            nparam.put("endDt", endDt+" 235959");
        }
        
        if(param.get("inviteCustName")!=null){
            sb.append(" AND info.invite_cust_name = :inviteCustName ");
            nparam.put("inviteCustName", param.get("inviteCustName"));
        }
        
        if(param.get("inviteCustMob")!=null){
            sb.append(" AND info.invite_cust_mob = :inviteCustMob ");
            nparam.put("inviteCustMob", param.get("inviteCustMob"));
        }
        
        if(param.get("inviteCustAddress")!=null){
            sb.append(" AND info.invite_cust_address like :inviteCustAddress ");
            nparam.put("inviteCustAddress", param.get("inviteCustAddress")+"%");
        }
        
        if(param.get("isRegister")!=null){
            sb.append(" AND info.is_register = :isRegister ");
            nparam.put("isRegister", param.get("isRegister"));
        }
        
        if(param.get("receiveCustName")!=null){
            sb.append(" AND recv.cust_name = :receiveCustName ");
            nparam.put("receiveCustName", param.get("receiveCustName"));
        }
        
        if(param.get("receiveCustMob")!=null){
            sb.append(" AND info.receive_cust_mob = :receiveCustMob");
            nparam.put("receiveCustMob", param.get("receiveCustMob"));
        }
        
        if(param.get("receiveCustAddress")!=null){
            sb.append(" AND info.receive_cust_address like :receiveCustAddress ");
            nparam.put("receiveCustAddress", param.get("receiveCustAddress")+"%");
        }
        
        if(param.get("inviteStatus")!=null){
            sb.append(" AND info.invite_status =:inviteStatus ");
            nparam.put("inviteStatus", param.get("inviteStatus"));
        }
        
        if(param.get("isRealNameCertify")!=null){
            sb.append(" AND recv.is_real_name_certify = :isRealNameCertify ");
            nparam.put("isRealNameCertify", param.get("isRealNameCertify"));
        }

        res.put("sql", sb.toString());
        res.put("param", nparam);
        return res;
    }

    @Override
    public Map<String, Object> getQueryMap(Map<String, Object> param) {
        Map<String, Object> obj = getWhereSql(param);
        StringBuffer sb = new StringBuffer(" select ");
        sb.append(" info.register_ts, ");//0
        sb.append(" info.receive_ts, ");//1
        sb.append(" recv.cust_name, ");//2
        sb.append(" info.receive_cust_mob, ");//3
        sb.append(" info.receive_cust_address, ");//4
        sb.append(" info.receive_cust_ip, ");//5
        sb.append(" info.invite_cust_name, ");//6
        sb.append(" info.invite_cust_mob, ");//7
        sb.append(" info.invite_cust_address, ");//8
        sb.append(" info.invite_cust_ip, ");//9
        sb.append(" info.is_register, ");//10
        sb.append(" info.invite_status, ");//11
        sb.append(" recv.is_real_name_certify, ");//12
        sb.append(" info.remark ");//13
        sb.append(getSearchSql());
        sb.append(obj.get("sql"));
        sb.append(" order by info.receive_ts desc ");

        StringBuilder countSb = new StringBuilder();
        countSb.append("SELECT COUNT(*) ");
        countSb.append(getSearchSql());
        countSb.append(obj.get("sql"));

        Map<String, Object> result = (Map<String, Object>) obj.get("param");

        result.put("QUERY_SQL",sb.toString());
        result.put("QUERY_COUNT_SQL",countSb.toString());

        return result;
    }
}
